﻿
CREATE PROCEDURE [dbo].[report_payed_input_invoices]
	(
	@p_date_from	date,
	@p_date_to		date,
	@p_nsi_organization_id	int,
	@p_contract_id	int,
	@p_folder_num	int,
	@p_nsi_type_of_activity_id	int,
	@p_contract_number	nvarchar(255),
	@p_xml_materials xml,
	@p_nsi_fin_res_id int,
	@p_nsi_expense_item_id int
	)
AS
	SET NOCOUNT ON
	
	declare @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml_materials
	
    SELECT ii.ID, ii.REG_NUMBER, ii.NUMBER, ii.[DATE], ii.CONTRACT_NUMBER, ii.CONTRACT_DATE, 
		o.SHORT_NAME as ORGANIZATION_NAME, o.COUNTRY as ORGANIZATION_COUNTRY, [dbo].[f_invoice_in_get_rest](ii.ID) as REST_SUM_RUR, 
		oo.SHORT_NAME as CUSTOMER, cn.NUMBER as CONTRACT_NUM_CUSTOMER, SUM(poi.SUM_RUB) as PAY_SUM
	FROM INVOICE_IN as ii
	LEFT JOIN NSI_ORGANIZATION as o
		ON o.id = ii.NSI_ORGANIZATION_ID
	INNER JOIN PAY_OUT_ON_INV_IN as poi
		ON poi.INVOICE_IN_ID = ii.ID
    INNER JOIN PAYMENT_OUT as po
		ON poi.PAYMENT_OUT_ID = po.ID
	LEFT JOIN NSI_FINANCE_RESOURCE as fi
		ON ii.NSI_FINANCE_RESOURCE_ID = fi.ID
	LEFT JOIN NSI_EXPENSE_ITEM as ei
		ON ii.NSI_EXPENSE_ITEM_ID = ei.ID
	LEFT JOIN [CONTRACT] as cn 
		ON cn.ID = ii.NSI_CONTRACT_ID
	LEFT JOIN NSI_ORGANIZATION as oo
		ON oo.ID = cn.NSI_ORGANIZATION_ID
	WHERE	
		(
		ii.[STATE] = 'Оплачен'
		OR
		ii.[STATE] = 'Оплачен частично'
		)		
		AND	po.DATE between @p_date_from AND @p_date_to
		AND
		(
			(@p_nsi_organization_id is null)
			OR
			(ii.NSI_ORGANIZATION_ID = @p_nsi_organization_id)			
		)
		AND
		(
			(@p_contract_id is null)
			OR
			(ii.NSI_CONTRACT_ID = @p_contract_id)
		)
		AND
		(
			(@p_folder_num is null)
			OR
			(ii.N_FOLDER = @p_folder_num)
		)
		AND
		(
			(@p_nsi_type_of_activity_id is null)
			OR
			(ii.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
		)
		AND
		(
			(@p_contract_number is null)
			OR
			(ii.CONTRACT_NUMBER LIKE @p_contract_number)
		)
		AND
		(
			(@p_xml_materials is null)
			OR
			(
				ii.ID IN (				
				SELECT DISTINCT iii.INVOICE_IN_ID FROM dbo.INVOICE_IN_ITEM as iii
				INNER JOIN OPENXML(@docHandle, N'/materials/material') WITH (id int) as x
					ON iii.NSI_MATERIAL_ID = x.ID
				)
			)
		)
		AND
		(
			(@p_nsi_fin_res_id is null)
			OR
			(fi.id = @p_nsi_fin_res_id)
		)
		AND
		(
			(@p_nsi_expense_item_id is null)
			OR
			(ei.id = @p_nsi_expense_item_id)
		)
	GROUP BY ii.REG_NUMBER, ii.ID, ii.NUMBER, ii.[DATE], ii.CONTRACT_NUMBER, ii.CONTRACT_DATE, o.SHORT_NAME, o.COUNTRY, oo.SHORT_NAME, cn.NUMBER 
	
	EXEC sp_xml_removedocument @docHandle